#!/usr/bin/python
# -*- coding: utf-8 -*-
import pyquery 
import sys,urllib
import datetime
import chardet
import MySQLdb  
import os
from time import sleep

#windows 任务计划默认是windows/system32的执行目录，必须在统一目录本程序才能通过SVN通用
os.chdir("D:\\python\\projects\\pythonwebgrab\\ArchContests")
#db operations
try:
    db = MySQLdb.connect(host='localhost',user='root',passwd='root',db='topcoder')
except Exception, e:
    print e
#conn = MySQLdb.connect('localhost','root','StanFord7','topcoder')   
cursor = db.cursor()    

def InsertContestDup( fields ):
    # Prepare SQL query to INSERT a record into the database.
    sql = "INSERT INTO `topcoder`.`archcontest` (`contestname`, \
             `registerby`, `submitby`, `payment`, `technology`, \
             `me`, `registrants`, `submissions`) VALUES \
             ( '%s','%s','%s','%s','%s','%s','%s',%d)  ON DUPLICATE KEY UPDATE registerby = '%s',me='%s',registrants='%s',submissions='%s'" \
    % (fields[0], fields[3],fields[4], fields[5], fields[6], fields[7],fields[8],int(fields[9]),fields[3],fields[7],fields[8],int(fields[9]))
   
    try:
       #print "sqltext:",sql
       # Execute the SQL command
       cursor.execute(sql)
       # Commit your changes in the database
       db.commit()
       print "Suc insert %s"%fields[0]
    except Exception, e:
       print "Exception:",e
       # Rollback in case there is any error
       db.rollback()


def InsertContestReplace( fields ):
    # Prepare SQL query to INSERT a record into the database.
    sql = "REPLACE INTO `topcoder`.`archcontest` (`contestname`, \
             `registerby`, `submitby`, `payment`, `technology`, \
             `me`, `registrants`, `submissions`) VALUES \
             ( '%s','%s','%s','%s','%s','%s','%s',%d)" \
    % (fields[0], fields[3],fields[4], fields[5], fields[6], fields[7],fields[8],int(fields[9]))
   
    try:
       #print "sqltext:",sql
       # Execute the SQL command
       cursor.execute(sql)
       # Commit your changes in the database
       db.commit()
       print "Suc insert"
    except Exception, e:
       print "Exception:",e
       # Rollback in case there is any error
       db.rollback()
       
base_url = 'http://community.topcoder.com'
#p = pyquery.PyQuery(base_url+'/tc?&sc=10&sd=desc&module=ViewAssemblyActiveContests')
#
#pollen_table = p('table.stat')
#pollen_rows = pollen_table('tr')[2:]
#for row in pollen_rows:
#     fields = row.getchildren()
    # print fields[0].text_content()
#    pollen_source = fields[0].text_content()
#    pollen_value = fields[1].getchildren()[0].get('width')
#    print pollen_source.strip(), int(pollen_value)

#print  pollen_table('tr')('td')('a').attr('href')
#print pollen_table('tr').eq(2)('td')('a').attr('href')
#print len(pollen_rows)

#     print len(fields)
# 
#     print fields[0].text_content()
#     print fields[0]('a').show()
#contestUrls = []
#registrantUrls = []
#techs = []
#i = 2
#while i <  len(pollen_rows) + 2:
#    contestUrl = pollen_table('tr').eq(i)('td')('a').attr('href')
#    contestUrls.append(base_url+contestUrl)
#    #
#    registrant = pollen_table('tr').eq(i)('td').eq(8)('a').attr('href')
#    registrantUrls.append(registrant)
#    i+=1
#
#for contestUrl in contestUrls:
#    #print contestUrl
#    contest = pyquery.PyQuery(contestUrl)
#    tech = contest('ul.noSpList')
#    techs.append(tech.eq(0).text())
#    print tech.eq(0).text()
#
#for registrantUrl in registrantUrls:
#    print registrantUrl
#    registrant = pyquery.PyQuery(base_url+registrantUrl)
#    people_table = registrant('table.statTable').eq(1)
#    people_rows = people_table('tr')[2:]
#    i = 2
#    while i <  len(people_rows) + 2:
#        people = people_table('tr').eq(i)('td')('a')
#        if people.text() == 'westine':
#            print people.text()
#        i+=1
   
url="http://community.topcoder.com/tc?module=ActiveContests&pt=7"
wp = urllib.urlopen(url)
print "start download..."
content = wp.read()
print len(content)

print content.count("Active Software Architecture Contests")
index =  content.find("Active Software Architecture Contests")
print index
contentPre = content[0:content.find("Active Software Architecture Contests")]
print len(contentPre)
indexPre = contentPre.rfind("<table")
print "indexPre" , indexPre
contentPost=content[content.find("Active Software Architecture Contests")+1:]
indexPost = contentPost.find("</table>") + index
print "indexPost",indexPost

contestContent = content[indexPre:indexPost]
#print contestContent
#content=content[content.find("href=")+7:content.find("target")-2]
#filename = content
#url ="http://www.putclub.com/"+content
#print content
#wp = urllib.urlopen(url)
#print "start download..."
#content = wp.read()
##print content
#print content.count("<div class=\"content\"")
##content = content[content.find("<div class=\"content\""):]
#content = content[content.find("<!--info end------->"):]
#content = content[:content.find("<div class=\"dede_pages\"")-1]
#filename = filename[filename.find("presidentspeech")+len("presidentspeech/"):]
#filename = filename.replace('/',"-",filename.count("/"))
now = datetime.datetime.now()
suffix = now.strftime("%Y%m%d_%H%M%S")
today = datetime.date.today()
print now,today,suffix
fp = open('ArchContests%s.html'%suffix,"w+")
print "current path: ",os.getcwd()

fp.write("<html>    ")
fp.write("<head>")
fp.write("<base href=\"http://community.topcoder.com/\"  target=\"_blank\"> ")
fp.write("</head><body>")
fp.write(contestContent)
fp.write("</html>   ")

print sys.getdefaultencoding()
fp.seek(0)
fpcontent = fp.read()

p = pyquery.PyQuery(fpcontent)   
#if isinstance(fp.read(), unicode):
#    print "is unicode"

print chardet.detect(fpcontent)
fp.close()
#print chardet.detect(p.outerHtml())

pollen_table = p('table.stat')
pollen_rows = pollen_table('tr')[2:]
title = pollen_table('tr').eq(0)('td').eq(0).text()
pollen_table('tr').eq(0)('td').eq(0).text(str(title)+"  =====> total contests: " +str(len(pollen_rows)))
print len(pollen_rows)
print p.text()
i = 2
while i <  len(pollen_rows) + 2:
    #print pollen_table('tr').eq(i).text()
    contestUrl = pollen_table('tr').eq(i)('td')('a').attr('href')
    contest = pyquery.PyQuery(base_url+contestUrl)
    tech = contest('ul.noSpList')
    pollen_table('tr').eq(i)('td').eq(6).text(tech.eq(0).text())
    #find the registrant infomation
    registrantUrl = pollen_table('tr').eq(i)('td').eq(8)('a').attr('href')
    print registrantUrl
    #there is the possibility that no one has registered this contest,so there isn't a registrantUrl currently
    if  registrantUrl is not None :
        registrant = pyquery.PyQuery(base_url+registrantUrl)
        people_table = registrant('table.statTable').eq(1)
        people_rows = people_table('tr')[2:]
        j = 2
        while j <  len(people_rows) + 2:
            people = people_table('tr').eq(j)('td')('a')
            if people.text() == 'westine':
              
                print people.text()
                pollen_table('tr').eq(i)('td').eq(7).text(people.text())
                dirname = pollen_table('tr').eq(i)('td').eq(0).text()
                dirname = "D:\\topcoder\\" + dirname
                print dirname
                try:
                   os.makedirs(dirname)
                except OSError:
                   if os.path.exists(dirname):
                       # We are nearly safe
                       pass
                   else:
                       # There was an error on creation, so make sure we know about it
                       raise
            j+=1
    i+=1

for i in  range(2,len(pollen_rows)+2):
    print pollen_table('tr').eq(i)('td').text()
    row_fields = pollen_table('tr').eq(i)('td')[0:]
#    print row_fields[0].text_content()
    print len(row_fields)
    #InsertContest(row_fields.gerchildren())
    for k in range(len(row_fields)):
        print pollen_table('tr').eq(i)('td').eq(k).text()

for i in range(2,len(pollen_rows)+2):
     fields = pollen_table('tr').eq(i)
     print len(fields('td')[:])
     insert_row_fields = []
     for j in range(len(fields('td')[:])):
          #  print fields[i].text_content() #because there is  &#160; in fields[2]
         if j != 1 and j != 2:
             insert_row_fields.append(fields('td').eq(j).text())
         else:
            insert_row_fields.append("")
     InsertContestDup(insert_row_fields)

fp = open('ArchContests%sNew.html'%suffix, 'w+')
print "current path: ",os.getcwd()
#sleep(20)
newfpcontent = (p)
fp.write(str(newfpcontent))
fp.close()




# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM archcontest "#\"
 #      " % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   print len(results)
   for row in results:
      fname = row[0]
      print fname
      #lname = row[1]
   #   age = row[2]
  #    sex = row[3]
 #     income = row[4]
      # Now print fetched result
  #    print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
   #          (fname, lname, age, sex, income )
   
except Exception, e:
    print "Exception:",e
#   print "Error: unable to fecth data"
# disconnect from server
db.close()
    